﻿using System;
using WorkOrder.Model;
using System.Collections;

namespace WorkOrder.Business
{
    public class Business_Table_Customer_Info
    {
        DbHelper dbhelper = new DbHelper();

        public bool Insert(Model_Table_Customer_Info model)
        {
            string sqlstr;
            string tmp1 = "insert into Table_Customer_Info (customer_info_id";
            string tmp2 = ") values (NEWID()";
            if (model.Acceptance_time.Year != 2)
            {
                tmp1 = tmp1 + ",acceptance_time";
                tmp2 = tmp2 + ",'" + model.Acceptance_time.ToString() + "'";
            }
            tmp1 = tmp1 + ",acceptanced";
            if (!model.Acceptanced)
                tmp2 = tmp2 + ",0";
            else
                tmp2 = tmp2 + ",1";

            if (!string.IsNullOrEmpty(model.Account_manager))
            {
                tmp1 = tmp1 + ",account_manager";
                tmp2 = tmp2 + ",'" + model.Account_manager + "'";
            }
            if (!string.IsNullOrEmpty(model.Account_manager_tell))
            {
                tmp1 = tmp1 + ",account_manager_tell";
                tmp2 = tmp2 + ",'" + model.Account_manager_tell + "'";
            }
            if (!string.IsNullOrEmpty(model.Customer_address))
            {
                tmp1 = tmp1 + ",customer_address";
                tmp2 = tmp2 + ",'" + model.Customer_address + "'";
            }
            if (!string.IsNullOrEmpty(model.Area_id))
            {
                tmp1 = tmp1 + ",area_id";
                tmp2 = tmp2 + "," + model.Area_id;
            }
            if (!string.IsNullOrEmpty(model.Customer_name))
            {
                tmp1 = tmp1 + ",customer_name";
                tmp2 = tmp2 + ",'" + model.Customer_name + "'";
            }
            if (!string.IsNullOrEmpty(model.Customer_tell))
            {
                tmp1 = tmp1 + ",customer_tell";
                tmp2 = tmp2 + ",'" + model.Customer_tell + "'";
            }
            if (!string.IsNullOrEmpty(model.Equipment_model))
            {
                tmp1 = tmp1 + ",equipment_model";
                tmp2 = tmp2 + ",'" + model.Equipment_model + "'";
            }
            if (!string.IsNullOrEmpty(model.Inte_unit_id))
            {
                tmp1 = tmp1 + ",inte_unit_id";
                tmp2 = tmp2 + "," + model.Inte_unit_id;
            }
            if (!string.IsNullOrEmpty(model.Period))
            {
                tmp1 = tmp1 + ",period";
                tmp2 = tmp2 + ",'" + model.Period + "'";
            }
            if (!string.IsNullOrEmpty(model.Station_name))
            {
                tmp1 = tmp1 + ",station_name";
                tmp2 = tmp2 + ",'" + model.Station_name + "'";
            }
            if (!string.IsNullOrEmpty(model.Product_id))
            {
                tmp1 = tmp1 + ",product_id";
                tmp2 = tmp2 + "," + model.Product_id;
            }
            if (!string.IsNullOrEmpty(model.Town_id))
            {
                tmp1 = tmp1 + ",town_id";
                tmp2 = tmp2 + "," + model.Town_id;
            }
            if (!string.IsNullOrEmpty(model.Operator))
            {
                tmp1 = tmp1 + ",operator";
                tmp2 = tmp2 + "," + model.Operator;
            }
            sqlstr = tmp1 + tmp2 + ");";
            if (dbhelper.ExecuteNoQuery(sqlstr) > 0)
                return true;
            else
                return false;
        }

        public bool Update(Model_Table_Customer_Info model)
        {
            string sqlstr;
            string tmp1 = "update Table_Customer_Info set ";
            string tmp2 = " where customer_info_id='" + model.Customer_info_id + "'";
            if (!model.Acceptanced)
            {
                tmp1 = tmp1 + "acceptanced=0,";
                tmp1 = tmp1 + "acceptance_time=NULL,";
            }
            else
            {
                tmp1 = tmp1 + "acceptanced=1,";
                if (model.Acceptance_time.Year != 2)
                {
                    tmp1 = tmp1 + "acceptance_time='" + model.Acceptance_time.ToString() + "',";
                }
                else
                {
                    tmp1 = tmp1 + "acceptance_time=NULL,";
                }
            }

            if (!string.IsNullOrEmpty(model.Account_manager))
                tmp1 = tmp1 + "account_manager='" + model.Account_manager + "',";
            else
                tmp1 = tmp1 + "account_manager=NULL,";

            if (!string.IsNullOrEmpty(model.Account_manager_tell))
                tmp1 = tmp1 + "account_manager_tell='" + model.Account_manager_tell + "',";
            else
                tmp1 = tmp1 + "account_manager_tell=NULL,";

            if (!string.IsNullOrEmpty(model.Customer_address))
                tmp1 = tmp1 + "customer_address='" + model.Customer_address + "',";
            else
                tmp1 = tmp1 + "customer_address=NULL,";

            if (!string.IsNullOrEmpty(model.Area_id))
                tmp1 = tmp1 + "area_id=" + model.Area_id + ",";
            else
                tmp1 = tmp1 + "area_id=NULL,";

            if (!string.IsNullOrEmpty(model.Customer_name))
                tmp1 = tmp1 + "customer_name='" + model.Customer_name + "',";
            else
                tmp1 = tmp1 + "customer_name=NULL,";

            if (!string.IsNullOrEmpty(model.Customer_tell))
                tmp1 = tmp1 + "customer_tell='" + model.Customer_tell + "',";
            else
                tmp1 = tmp1 + "customer_tell=NULL,";

            if (!string.IsNullOrEmpty(model.Equipment_model))
                tmp1 = tmp1 + "equipment_model='" + model.Equipment_model + "',";
            else
                tmp1 = tmp1 + "equipment_model=NULL,";

            if (!string.IsNullOrEmpty(model.Inte_unit_id))
                tmp1 = tmp1 + "inte_unit_id=" + model.Inte_unit_id + ",";
            else
                tmp1 = tmp1 + "inte_unit_id=NULL,";

            if (!string.IsNullOrEmpty(model.Period))
                tmp1 = tmp1 + "period='" + model.Period + "',";
            else
                tmp1 = tmp1 + "period=NULL,";

            if (!string.IsNullOrEmpty(model.Station_name))
                tmp1 = tmp1 + "station_name='" + model.Station_name + "',";
            else
                tmp1 = tmp1 + "station_name=NULL,";

            if (!string.IsNullOrEmpty(model.Product_id))
                tmp1 = tmp1 + "product_id=" + model.Product_id + ",";
            else
                tmp1 = tmp1 + "product_id=NULL,";

            if (!string.IsNullOrEmpty(model.Town_id))
                tmp1 = tmp1 + "town_id=" + model.Town_id + ",";
            else
                tmp1 = tmp1 + "town_id=NULL,";

            if (!string.IsNullOrEmpty(model.Operator))
                tmp1 = tmp1 + "operator=" + model.Operator + ",";
            else
                tmp1 = tmp1 + "operator=NULL,";

            sqlstr = tmp1.Remove(tmp1.Length - 1, 1) + tmp2;
            if (dbhelper.ExecuteNoQuery(sqlstr) > 0)
                return true;
            else
                return false;
        }

        public System.Data.DataTable QueryCustomerInfo(string condition)
        {
            string sqlstr = "select A.*,convert(varchar(10),A.acceptance_time,120) as acceptance_time_b,B.area_name,C.town_name,D.inte_unit_name,E.product_name,F.remark as name_remark from Table_Customer_Info A left join " +
                "Table_Area B on A.area_id=B.area_id left join Table_Town C on A.town_id=C.town_id left join Table_Integrated_unit D on A.inte_unit_id=D.inte_unit_id left join Table_Product E on A.product_id=E.product_id left join Table_User F " +
                 "on A.operator=F.[user_id] where " + condition;
            return dbhelper.Query("changeovertable", sqlstr);
        }

        public Model_Table_Customer_Info GetCustomerInfo(string id)
        {
           string sql="select * from Table_Customer_Info where customer_info_id='" + id + "'";
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sql))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Customer_Info tmp=new Model_Table_Customer_Info();
                    if (rdr["customer_info_id"] != DBNull.Value)
                        tmp.Customer_info_id = rdr["customer_info_id"].ToString();
                    else
                        tmp.Customer_info_id = "";
                    if (rdr["station_name"] != DBNull.Value)
                        tmp.Station_name = rdr["station_name"].ToString();
                    else
                        tmp.Station_name = "";
                    if (rdr["product_id"] != DBNull.Value)
                        tmp.Product_id = rdr["product_id"].ToString();
                    else
                        tmp.Product_id = "";
                    if (rdr["area_id"] != DBNull.Value)
                        tmp.Area_id = rdr["area_id"].ToString();
                    else
                        tmp.Area_id = "";
                    if (rdr["town_id"] != DBNull.Value)
                        tmp.Town_id = rdr["town_id"].ToString();
                    else
                        tmp.Town_id = "";
                    if (rdr["customer_address"] != DBNull.Value)
                        tmp.Customer_address = rdr["customer_address"].ToString();
                    else
                        tmp.Customer_address = "";
                    if (rdr["customer_name"] != DBNull.Value)
                        tmp.Customer_name = rdr["customer_name"].ToString();
                    else
                        tmp.Customer_name = "";
                    if (rdr["customer_tell"] != DBNull.Value)
                        tmp.Customer_tell = rdr["customer_tell"].ToString();
                    else
                        tmp.Customer_tell = "";
                    if (rdr["account_manager"] != DBNull.Value)
                        tmp.Account_manager = rdr["account_manager"].ToString();
                    else
                        tmp.Account_manager = "";
                    if (rdr["account_manager_tell"] != DBNull.Value)
                        tmp.Account_manager_tell = rdr["account_manager_tell"].ToString();
                    else
                        tmp.Account_manager_tell = "";
                    if (rdr["equipment_model"] != DBNull.Value)
                        tmp.Equipment_model = rdr["equipment_model"].ToString();
                    else
                        tmp.Equipment_model = "";
                    if (rdr["inte_unit_id"] != DBNull.Value)
                        tmp.Inte_unit_id = rdr["inte_unit_id"].ToString();
                    else
                        tmp.Inte_unit_id = "";
                    if (rdr["period"] != DBNull.Value)
                        tmp.Period = rdr["period"].ToString();
                    else
                        tmp.Period = "";
                    if (rdr["acceptanced"] != DBNull.Value)
                        tmp.Acceptanced = Convert.ToBoolean(rdr["acceptanced"]);
                    else
                        tmp.Acceptanced = false;
                    if (rdr["acceptance_time"] != DBNull.Value)
                        tmp.Acceptance_time = Convert.ToDateTime(rdr["acceptance_time"]);
                    else
                        tmp.Acceptance_time = new DateTime(2, 2, 2);
                    if (rdr["operator"] != DBNull.Value)
                        tmp.Operator = rdr["operator"].ToString();
                    else
                        tmp.Operator = "";

                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else return null;
            }
        }

        #region 返回集成单位资料
        public System.Data.DataSet GetIntegratedUnit()
        {
            string sqlstr = "select inte_unit_id,inte_unit_name from Table_Integrated_unit";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回产品类别资料
        public System.Data.DataSet GetProduct()
        {
            string sqlstr = "select product_id,product_name from Table_Product";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 根据id返回产品类别
        public Model_Table_Product GetProductByID(string id)
        {
            string sqlstr = "select * from Table_Product where product_id=" + id;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Product tmp = new Model_Table_Product(rdr["product_id"].ToString(), rdr["product_name"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion

        #region 返回镇区资料
        public System.Data.DataSet GetTown()
        {
            string sqlstr = "select town_id,town_name from Table_Town";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回区域资料
        public System.Data.DataSet GetArea()
        {
            string sqlstr = "select area_id,area_name from Table_Area";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 根据条件返回区域
        public Model_Table_Area GetArea(string condition)
        {
            if (string.IsNullOrEmpty(condition)) condition = "1=1";
            string sqlstr = "select * from Table_Area where " + condition;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Area tmp = new Model_Table_Area(rdr["area_id"].ToString(), rdr["area_name"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion

        #region 根据镇区返回区域
        public Model_Table_Area GetAreaByTown(string townid)
        {
            if (!string.IsNullOrEmpty(townid))
            {
                string sqlstr = "select * from Table_Area,Table_Town where Table_Area.area_id=Table_Town.area_id and Table_Town.town_id=" + townid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    if (rdr != null && rdr.Read())
                    {
                        Model_Table_Area tmp = new Model_Table_Area(rdr["area_id"].ToString(), rdr["area_name"].ToString(), rdr["remark"].ToString());
                        rdr.Close();
                        dbhelper.CloseDbConnection();
                        return tmp;
                    }
                    else
                        return new Model_Table_Area("", "", "");
                }
            }
            else return new Model_Table_Area("", "", "");
        }
        #endregion

        #region 根据区域返回镇区
        public ArrayList GetTownByArea(string areaid)
        {
            if (!string.IsNullOrEmpty(areaid))
            {
                ArrayList arr = new ArrayList();
                string sqlstr = "select town_id,town_name from Table_Town where area_id=" + areaid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    if (rdr != null)
                    {
                        while (rdr.Read())
                        {
                            object[] tmp = { rdr["town_id"], rdr["town_name"] };
                            arr.Add(tmp);
                        }
                        rdr.Close();
                        dbhelper.CloseDbConnection();
                        return arr;
                    }
                    else
                        return null;
                }
            }
            else return null;
        }
        #endregion

        #region 根据区域返回镇区ID
        public ArrayList GetTownIdByArea(string areaid)
        {
            if (!string.IsNullOrEmpty(areaid))
            {
                ArrayList arr = new ArrayList();
                string sqlstr = "select town_id from Table_Town where area_id=" + areaid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    if (rdr != null)
                    {
                        while (rdr.Read())
                        {
                            arr.Add(rdr["town_id"]);
                        }
                        rdr.Close();
                        dbhelper.CloseDbConnection();
                        return arr;
                    }
                    else
                        return null;
                }
            }
            else return null;
        }
        #endregion

        #region 根据id返回镇区
        public Model_Table_Town GetTownByID(string id)
        {
            string sqlstr = "select * from Table_Town where town_id=" + id;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Town tmp = new Model_Table_Town(rdr["town_id"].ToString(), rdr["area_id"].ToString(), rdr["town_name"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion
    }
}
